import requests
import xlrd
import json
import xlwt
import pymysql
import threading

def import_data():
    wb = xlrd.open_workbook('D:/PyProject/SinoMed/Test1.xls')
    ws = wb.sheets()[0]
    nrows = ws.nrows
    # print(nrows)
    company_names = ws.col_values(1, 1, nrows)
    company_ids = ws.col_values(2, 1, nrows)
    exchanges = ws.col_values(3, 1, nrows)
    return company_names, company_ids, exchanges

def get_info(url):
    r = requests.get(url)
    #print(r.text)
    if len(r.text) > 200:
        data1 = json.loads(r.text).get('data')[0]
        #print(data1)
        read_content(data1)

def read_content(data1):
    names = locals()
    keys = []
    values = []
    for key, value in data1.items():
        #print(key)
        names[str(key)] = value
        # exec('var{}={}'.format(key, key))
    for key, value in names.items():
        #print(str(key) + "=" + str(value))
        if "'" in str(value):
            value = str(value).replace("'", "")
        elif "None" in str(value):
            value = str(value).replace("None", "")
        keys.append(key)
        values.append(value)
    keys = keys[1:]
    values = values[1:]
    #print(keys, values)
    write_in_mysql(keys, values)

def run_sql(sql):
    # 初始化数据库配置
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        db.commit()
    except Exception as e:
        print(e)
        #db.rollback()
        #raise (e)

def write_in_mysql(keys, values):

    #将第一个字段数值添加进表格，keys[0] #证券码
    sql = "INSERT INTO %s (%s) VALUES('%s')" % (table_name, keys[0], values[0])
    run_sql(sql)
    print(sql)
    #将第六个字段数值添加进表格，keys[5] #报告日期
    sql = "INSERT INTO %s (%s) VALUES('%s')" % (table_name, keys[5], values[5])
    run_sql(sql)
    print(sql)

    #根据第一个字段和第五个字段数值判断行数，循环update之后所有的字段数值, 循环列表values
    for i, value in enumerate(values):
        if i>0:
            sql = "UPDATE %s SET %s='%s' WHERE SECUCODE='%s' AND REPORT_DATE='%s'" % (table_name, keys[i], values[i], values[0], values[5])
            run_sql(sql)
            print(sql)
    print("Done")

    """
    SECUCODE = i.get("SECUCODE")  # 证券码
    SECURITY_CODE = i.get("SECURITY_CODE")  # 证券代码
    SECURITY_NAME_ABBR = i.get("SECURITY_NAME_ABBR") #证券名缩写
    ORG_CODE = i.get("ORG_CODE") #组织代码
    ORG_TYPE = i.get("ORG_TYPE") #组织类型
    REPORT_DATE = i.get("REPORT_DATE") #报告日期
    REPORT_TYPE = i.get("REPORT_TYPE") #报告种类
    REPORT_DATE_NAME = i.get("REPORT_DATE_NAME") #报告日期名称
    SECURITY_TYPE_CODE = i.get("SECURITY_TYPE_CODE") #证券种类代码
    NOTICE_DATE = i.get("NOTICE_DATE") #公告日期
    UPDATE_DATE = i.get("UPDATE_DATE") #更新日期
    CURRENCY = i.get("CURRENCY") #货币

    #每股指标
    EPSJB = i.get("EPSJB") #基本每股收益（元）
    EPSKCJB = i.get("EPSKCJB") #扣非每股收益（元）
    EPSXS = i.get("EPSXS") #稀释每股收益（元）
    BPS = i.get("BPS") #每股净资产（元）
    MGZBGJ = i.get("MGZBGJ") #每股公积金（元）
    MGWFPLR = i.get("MGWFPLR") #每股未分配利润（元）
    MGJYXJJE = i.get("MGJYXJJE") #每股经营现金流（元）

    #成长能力指标
    TOTALOPERATEREVE = i.get("TOTALPERATEREVE") #营业总收入（元）
    MLR = i.get("MLR") #
    PARENTNETPROFIT = i.get("PARENTNETPROFIT") #归属净利润（元）
    KCFJCXSYJLR = i.get("KCFJCXSYJLR") #扣非净利润（元）
    TOTALOPERATEREVETZ = i.get("TOTALOPERATEREVETZ") #营业总收入同比增长（%）
    PARENTNETPROFITTZ = i.get("PARENTNETPROFITTZ") #归属净利润同比增长（%）
    KCFJCXSYJLRTZ = i.get("KCFJCXSYJLRT") #扣非净利润同比增长（%）
    YYZSRGDHBZC = i.get("YYZSRGDHBZC") #营业总收入滚动环比增长（%）
    NETPROFITRPHBZC = i.get("NETPROFITRPHBZC") #归属净利润滚动环比增长（%）
    KFJLRGDHBZC = i.get("KFJLRGDHBZC") #扣非净利润滚动环比增长（%）

    #盈利能力指标
    ROEJQ = i.get("ROEJQ") #净资产收益率（加权）（%）
    ROEKCJQ = i.get("ROEKCJQ") #净资产收益率（扣非/加权)（%）
    ZZCJLL = i.get("ZZCJLL") #总资产收益率（加权）（%）
    XSJLL = i.get("XSJLL") #净利率（%）
    XSMLL = i.get("XSMLL") #毛利率（%）
    YSZKYYSR = i.get("YSZKYYSR")
    XSJXLYYSR = i.get("XSJXLYYSR") #
    JYXJLYYSR = i.get("JYXJLYYSR") #
    TAXRATE = i.get("TAXRATE")

    #财务风险指标
    LD = i.get("LD") #流动比率
    SD = i.get("SD") #速动比率
    XJLLB = i.get("XJLLB") #现金流量比率
    ZCFZL = i.get("ZCFZL") #资产负债率（%）
    QYCS = i.get("QYCS") #权益乘数
    CQBL = i.get("CQBL") #产权比率
    ZZCZZTS = i.get("ZZCZZTS") #总资产周转天数
    CHZZTS = i.get("CHZZTS") #存货周转天数（天）
    YSZKZZTS = i.get("YSZKZZTS") #应收账款周转天数（天）
    TOAZZL = i.get("TOAZZL") #总资产周转率（次）
    CHZZL = i.get("CHZZL") #存货周转率（次）
    YSZKZZL = i.get("YSZKZZL") #应收账款周转率（次）

    TOTALDEPOSITS = i.get("TOTALDEPOSITS")
    GROSSLOANS = i.get("GROSSLOANS")
    LTDRR = i.get("LTDRR")
    NEWCAPITALADER = i.get("NEWCAPITALADER")
    HXYJBCZL = i.get("HXYJBCZL")
    NONPERLOAN = i.get("NONPERLOAN")
    BLDKBBL = i.get("BLDKBBL")
    NZBJE = i.get("NZBJE")
    TOTAL_ROI = i.get("TOTAL_ROI")
    NET_ROI = i.get("NET_ROI")
    EARNED_PREMIUM = i.get("EARNED_PREMIUM")
    COMPENSATE_EXPENSE = i.get("COMPENSATE_EXPENSE")
    SURRENDER_RATE_LIFE = i.get("SURRENDER_RATE_LIFE")
    SOLVENCY_AR = i.get("SOLVENCY_AR")
    JZB = i.get("JZB")
    JZC = i.get("JZC")
    JZBJZC = i.get("JZBJZC")
    ZYGPGMJZC = i.get("ZYGPGMJZC")
    ZYGDSYLZQJZB = i.get("ZYGDSYLZQJZB")
    YYFXZB = i.get("YYFXZB")
    JJYWFXZB = i.get("JJYWFXZB")
    ZQZYYWFXZB = i.get("ZQZYYWFXZB")
    ZQCXYWFXZB = i.get("ZQCXYWFXZB")
    RZRQYWFXZB = i.get("RZRQYWFXZB")
    EPSJBTZ = i.get("EPSJBTZ")
    BPSTZ = i.get("BPSTZ")
    MGZBGJTZ = i.get("MGZBGJTZ")
    MGWFPLRTZ = i.get("MGWFPLRTZ")
    ROEJQTZ = i.get("ROEJQTZ")
    ZZCJLLTZ = i.get("ZZCJLLTZ")
    ZCFZLTZ = i.get("ZCFZLTZ")
    REPORT_YEAR = i.get("REPORT_YEAR")
    ROIC = i.get("ROIC")
    ROICTZ = i.get("ROICTZ")
    NBV_LIFE = i.get("NBV_LIFE")
    NBV_RATE = i.get("NBV_RATE")
    NHJZ_CURRENT_AMT = i.get("NHJZ_CURRENT_AMT")
    """
def initialize_table(table_name):

    # 此处手动建一个表格，确保表格中有一个字段即可#
    print(table_name)
    sql = "CREATE TABLE %s (Initial int(2))" % (table_name)
    print(sql)
    run_sql(sql)

    url_temp = "http://emweb.securities.eastmoney.com/PC_HSF10/NewFinanceAnalysis/ZYZBAjaxNew?type=0&code=SH603963"
    r = requests.get(url_temp)
    data1 = json.loads(r.text).get('data')[0]

    names = locals()
    keys = []
    values = []
    for key, value in data1.items():
        # print(key)
        names[str(key)] = value
        # exec('var{}={}'.format(key, key))
    for key, value in names.items():
        # print(str(key) + "=" + str(value))
        if "'" in str(value):
            value = str(value).replace("'", "")
        elif "None" in str(value):
            value = str(value).replace("None", "")
        keys.append(key)
        values.append(value)
    keys = keys[5:]
    values = values[5:]
    print(keys)
    print(values)
    print(len(values))
    # 将所有字段名添加到表格里，循环列表keys
    for i, value in enumerate(values):
        print(i)
        sql = "ALTER TABLE %s ADD " % (table_name) + str(keys[i]) + " TEXT"
        print(sql)
        run_sql(sql)

class myThreading(threading.Thread):
    def __init__(self, name, url):
        threading.Thread.__init__(self)
        self.thread_name = name
        self.url = url
    def run(self):
        print(self.name)
        get_info(url)


if __name__ == '__main__':
    db = pymysql.connect(host='localhost',
                         user='root',
                         password='aa115541',
                         database='SINOMED')
    cursor = db.cursor()
    company_names, company_ids, exchanges = import_data()
    #table_name = input("输入数据所在表格名称:")
    table_name = "123"
    threads = []
    initialize_table(table_name)
    #print(company_names, company_ids, exchanges)
    print(globals())
    for i, company_id in enumerate(company_ids):
        url = "http://emweb.securities.eastmoney.com/PC_HSF10/NewFinanceAnalysis/ZYZBAjaxNew?type=0&code=" + exchanges[i][0:2] + str(company_id)
        print(url)
    #url = "http://emweb.securities.eastmoney.com/PC_HSF10/NewFinanceAnalysis/ZYZBAjaxNew?type=0&code=SH603963"
    #print(url)

        thread = myThreading(i, url)
        threads.append(thread)

    for thread in threads:
        thread.start()

    for thread in threads:
        thread.join()
